﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.JsonPatch.Adapters;
using Microsoft.AspNetCore.Mvc;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using VOL.Core.DBManager;
using Dapper;
using System.Data;
using VOL.MyTable.Services;
using VOL.Entity.DomainModels;
using VOL.MyTable.Repositories;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;

namespace VOL.WebApi.Controllers.MyTable.bigscreen
{
    [Route("api/wholeVehicleScreen")]
    [ApiController]
    public class WholeVehicleScreenController : Controller
    {
        /**
         * 查询下订、排产、下线、发车的订单量
         * 参数：month:本月、year:本年
         * 参数示例：本月（202304），本年（2023）
         */
        [HttpPost, HttpGet, Route("getTotal"), AllowAnonymous]
        public object getTotal(string year)
        {
            Hashtable rtn_hst = new Hashtable();
           
            // 查询总销售额
            string totalSalesNum = "select sum(T1.Order_Amount) totalSalesNum from tt_sales_order t1 inner join TT_ORDER_CHECK t2 on T1.order_id=t2.order_id  where 1=1 and  T1.order_type='10771003'and T2.check_status='10491005' " +
                "and to_char(t1.update_date,'yyyy') = " + year;
            totalSalesNum = totalSalesNum.Replace("'", "''");
            List<dynamic> totalSalesNumList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([dms],'" + totalSalesNum + "')", "").ToList();
            rtn_hst.Add("totalSalesNumList", totalSalesNumList);

            // 查询总订单量
            string totalOrderNum = "select sum(t1.order_quantity) totalOrderNum from tt_sales_order t1 left join TT_ORDER_CHECK t2 on T1.order_id=t2.order_id where 1= 1 and T1.order_type='10771003' and T2.check_status='10491005'" +
                 "and to_char(t1.update_date,'yyyy') = " + year;
            totalOrderNum = totalOrderNum.Replace("'", "''");
            List<dynamic> totalOrderNumList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([dms],'" + totalOrderNum + "')", "").ToList();
            rtn_hst.Add("totalOrderNumList", totalOrderNumList);

            return rtn_hst;
        }

        [HttpPost, HttpGet, Route("getWholeVehicleProd"), AllowAnonymous]
        public object getWholeVehicleProd(string month, string year)//科目分类
        {
            Hashtable rtn_hst = new Hashtable();                                                                                                                            //Console.WriteLine(GET_SQL_TXT_FROM_DB("8"));

            // 查询本月下订
            string wholeVehicleMonthOrderSql = "select 'xdMonth' xdMonth,sum(t1.order_quantity) wholeVehicleMonthOrder from tt_sales_order t1 left join TT_ORDER_CHECK t2 on T1.order_id=t2.order_id where 1= 1 and T1.order_type='10771003' and T2.check_status='10491005'" +
                "and to_char(t1.update_date,'yyyymm') = " + month;
            wholeVehicleMonthOrderSql = wholeVehicleMonthOrderSql.Replace("'", "''");
            List<dynamic> wholeVehicleMonthOrderList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([dms],'" + wholeVehicleMonthOrderSql + "')", "").ToList();
            rtn_hst.Add("wholeVehicleMonthOrderList", wholeVehicleMonthOrderList);

            // 查询本年度下订
            string wholeVehicleYearOrderSql = "select 'xdYear' xdYear,sum(t1.order_quantity) wholeVehicleYearOrder from tt_sales_order t1 left join TT_ORDER_CHECK t2 on T1.order_id=t2.order_id where 1= 1 and T1.order_type='10771003' and T2.check_status='10491005'" +
                 "and to_char(t1.update_date,'yyyy') = " + year;
            wholeVehicleYearOrderSql = wholeVehicleYearOrderSql.Replace("'", "''");
            List<dynamic> wholeVehicleYearOrderList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([dms],'" + wholeVehicleYearOrderSql + "')", "").ToList();
            rtn_hst.Add("wholeVehicleYearOrderList", wholeVehicleYearOrderList);

            // 查询本月排产
            string wholeVehicleMonthProdSql = "select 'pcMonth' pcMonth,count(vin) wholeVehicleMonthProd from prodfact t where t.Facttype = '05' " +
                "and to_char(to_date(substr(t.time,0,6),'yyyymm'),'yyyymm')  =" + month;
            wholeVehicleMonthProdSql = wholeVehicleMonthProdSql.Replace("'", "''");
            List<dynamic> wholeVehicleMonthProdList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([MES],'" + wholeVehicleMonthProdSql + "')", "").ToList();
            rtn_hst.Add("wholeVehicleMonthProdList", wholeVehicleMonthProdList);

            // 查询本年度排产
            string wholeVehicleYearProdSql = "select 'pcYear' pcYear,count(vin) wholeVehicleYearProd from prodfact t where t.Facttype = '05' " +
                "and to_char(to_date(substr(t.time,0,4),'yyyy'),'yyyy')  =" + year;
            wholeVehicleYearProdSql = wholeVehicleYearProdSql.Replace("'", "''");
            List<dynamic> wholeVehicleYearProdList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([mes],'" + wholeVehicleYearProdSql + "')", "").ToList();
            rtn_hst.Add("wholeVehicleYearProdList", wholeVehicleYearProdList);

            // 查询本月下线
            string wholeVehicleProdMonthOfflineSql = "select 'xxMonth' xxMonth,count(vin) wholeVehicleProdMonthOffline from prodfact t where t.Facttype = '14' " +
                "and to_char(to_date(substr(t.time,0,6),'yyyymm'),'yyyymm') = " + month;
            wholeVehicleProdMonthOfflineSql = wholeVehicleProdMonthOfflineSql.Replace("'", "''");
            List<dynamic> wholeVehicleProdMonthOfflineList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([mes],'" + wholeVehicleProdMonthOfflineSql + "')", "").ToList();
            rtn_hst.Add("wholeVehicleProdMonthOfflineList", wholeVehicleProdMonthOfflineList);

            // 查询本年度下线
            string wholeVehicleProdYearOffLIineSql = "select 'xxYear' xxYear,count(vin) wholeVehicleProdYearOffLIine from prodfact t where t.Facttype = '14' " +
                "and to_char(to_date(substr(t.time,0,4),'yyyy'),'yyyy')  =" + year;
            wholeVehicleProdYearOffLIineSql = wholeVehicleProdYearOffLIineSql.Replace("'", "''");
            List<dynamic> wholeVehicleProdYearOffLIineList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([mes],'" + wholeVehicleProdYearOffLIineSql + "')", "").ToList();
            rtn_hst.Add("wholeVehicleProdYearOffLIineList", wholeVehicleProdYearOffLIineList);

            rtn_hst.Add("wholeVehicleProdMonthStartCarList", wholeVehicleProdYearOffLIineList);
            rtn_hst.Add("wholeVehicleProdYearStartCarList", wholeVehicleProdYearOffLIineList);

            return rtn_hst;
        }

        /**
         * 今、去年每月的接单台数
         */
        [HttpPost, HttpGet, Route("getReceiveOrderData"), AllowAnonymous]
        public object getReceiveOrderData()//成本中心分类
        {
            Hashtable rtn_hst = new Hashtable();
            // 去年每月接单台数
            string lastYearSql = "select  numl,dat,year from (select sum(b.ORDER_QUANTITY) - sum(nvl(b.cancel_Quantity, 0)) numl,to_char(check_date, 'mm') dat,to_char( check_date, 'yyyy' ) year from TT_ORDER_CHECK a, TT_SALES_ORDER b where a.order_id = b.order_id and check_status = 10491005  and to_char(check_date, 'yyyymm') >= TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-12),'year'),'yyyymm') and to_char(check_date, 'yyyymm')<=TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-12),'year'),'yyyymm')+11 group by to_char(check_date, 'mm'),to_char( check_date, 'yyyy' )  order by to_char(check_date, 'mm'),to_char( check_date, 'yyyy' )  asc) tab1 where rownum<=12 order by dat ";
            lastYearSql = lastYearSql.Replace("'", "''");
            List<dynamic> lastYearSqlList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([DMS],'" + lastYearSql + "')", "").ToList();
            rtn_hst.Add("lastYearSqlList", lastYearSqlList);

            // 今年每月接单台数
            string newYearSql = "select  numl,dat,year from ( select sum(b.ORDER_QUANTITY) - sum(nvl(b.cancel_Quantity, 0)) numl,to_char(check_date, 'mm') dat,to_char( check_date, 'yyyy' ) year from TT_ORDER_CHECK a, TT_SALES_ORDER b where a.order_id = b.order_id and check_status = 10491005  and to_char(check_date, 'yyyymm') >= TO_CHAR(TRUNC(SYSDATE,'year'),'yyyymm') and to_char(check_date, 'yyyymm')<=TO_CHAR(TRUNC(SYSDATE,'year'),'yyyymm')+11 group by to_char(check_date, 'mm'),to_char( check_date, 'yyyy' )  order by to_char(check_date, 'mm'),to_char( check_date, 'yyyy' )  ASC ) tab1 where rownum<=12 order by dat ";
            newYearSql = newYearSql.Replace("'", "''");
            List<dynamic> newYearSqlList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([DMS],'" + newYearSql + "')", "").ToList();
            rtn_hst.Add("newYearSqlList", newYearSqlList);

            //年份
            
            return rtn_hst;
        }
        
        /**
         * 查询近12个月的实销数据
         */
        [HttpPost, HttpGet, Route("getActualSalesData"), AllowAnonymous]
        public object getActualSalesData()//成本中心分类
        {
            Hashtable rtn_hst = new Hashtable();
            string lastYearSql = "select numr,dat from (SELECT count(distinct A.VIN) numr, TO_CHAR(A.PURCHASED_DATE, 'yyyyMM') dat FROM TM_SV_VEHICLE A WHERE A.CAR_STATUS = 10641006 AND A.IS_DELETE = 0  group by TO_CHAR(A.PURCHASED_DATE, 'yyyyMM') order by TO_CHAR(A.PURCHASED_DATE, 'yyyyMM') desc) where rownum<=12 order by dat asc";
            lastYearSql = lastYearSql.Replace("'", "''");
            List<dynamic> lastYearSqlList = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([DMS],'" + lastYearSql + "')", "").ToList();
            rtn_hst.Add("lastYearSqlList", lastYearSqlList);

            return rtn_hst;
        }


    }
}
